import web
import src.sql as sql
import src.conf_buffer as conf
import src.ipcalc as ipcalc
import psycopg2.pool

urls = (
	'/', 'index',
	'/login','login',
	'/logout','logout',
	'/group','group',
	'/maps','maps'
)

conn_string="dbname='%s' user='%s' password='%s' host='%s'" % (conf.POSTGRESQL_DATABASE,conf.POSTGRESQL_USERNAME,conf.POSTGRESQL_PASSWORD,conf.POSTGRESQL_HOST)
render=web.template.render("src/templates/",globals={'IP':ipcalc.IP,'str':str})
pool=sql.SQL_MODULE.pool.SimpleConnectionPool(1,5,conn_string)
def checkauthorisation():
	conn = pool.getconn()
	cur=conn.cursor()
	cur.execute("SELECT value FROM login WHERE property='uuid'")
	result = cur.fetchone()[0]
	cur.close()
	pool.putconn(conn)
	if not (web.cookies().get("uuid")==result):
		web.seeother("/login?fail=1")
def getgroupstable():
	conn = pool.getconn()
	cur=conn.cursor()
	cur.execute("SELECT * FROM groups")
	table=render.groupstable(cur)
	cur.close()
	pool.putconn(conn)
	return table
class group:
	def GET(self):
		checkauthorisation()
		return render.layout(getgroupstable())
	def POST(self):
		checkauthorisation()
		if web.input(Action=None).Action == "Add":
			querystring="INSERT INTO groups (name,start_ip_int,end_ip_int,gateway,domain_name,domain_name_servers,ntp_servers) VALUES ('%s',%s,%s,'%s','%s','%s','%s')" % (web.input().name,int(ipcalc.IP(web.input().start_ip_int)),int(ipcalc.IP(web.input().end_ip_int)),web.input().gateway,web.input().domain_name,web.input().domain_name_servers,web.input().ntp_servers)
			conn = pool.getconn()
			cur=conn.cursor()
			cur.execute(querystring)
			conn.commit()
			cur.close()
			pool.putconn(conn)
			return render.layout(getgroupstable())
		elif web.input(Action=None).Action == "Update":
			querystring="UPDATE groups SET name='%s',start_ip_int=%s, end_ip_int=%s, gateway='%s', domain_name='%s', domain_name_servers='%s' ,ntp_servers='%s' WHERE id=%s" % (web.input().name,int(ipcalc.IP(web.input().start_ip_int)),int(ipcalc.IP(web.input().end_ip_int)), web.input().gateway, web.input().domain_name, web.input().domain_name_servers, web.input().ntp_servers,web.input().id)
			conn = pool.getconn()
			cur=conn.cursor()
			cur.execute(querystring)
			conn.commit()
			cur.close()
			pool.putconn(conn)
			return render.layout(getgroupstable())
		elif web.input(Action=None).Action == "Delete":
			querystring="DELETE FROM groups WHERE  id=%s" % (web.input().id,)
			conn = pool.getconn()
			cur=conn.cursor()
			cur.execute(querystring)
			conn.commit()
			cur.close()
			pool.putconn(conn)
			return render.layout(getgroupstable())

def getsubnetstable():
	conn = pool.getconn()
	cur=conn.cursor()
	cur.execute("SELECT subnet, serial, lease_time, subnet_mask, broadcast_address FROM subnets")
	row=cur.fetchone();
	table=render.subnets(row)
	cur.close()
	pool.putconn(conn)
	return table

def getindexpage():
	return render.layout(str(getsubnetstable()))

class index:
	def GET(self):
		checkauthorisation()
		return getindexpage()
	def POST(self):
		checkauthorisation()
		if not web.input(submit_subnet=None).submit_subnet is None:
			cur.execute("UPDATE subnets SET subnet=%s, serial=%s, lease_time=%s, subnet_mask=%s, broadcast_address=%s",(web.input().subnet, web.input().serial, web.input().lease_time, web.input().subnet_mask, web.input().broadcast_address))
			conn.commit()
			cur.close()
			pool.putconn(conn)
			return getindexpage()
		
password_form=web.form.Form(
	web.form.Password("password", description="Password"),
	web.form.Button("submit", type="submit", description="Submit")
)
class login:
	def GET(self):
		f = password_form()
		r = f.render()
		form="<form name='password' method='POST'>%s</form>" % (r,)
		return render.layout(form)
		
	def POST(self):
		f = password_form()
		password=web.input().password
		conn = pool.getconn()
		cur=conn.cursor()
		cur.execute("SELECT value FROM login WHERE property='password'")
		result = cur.fetchone()[0]
		if(password==result):
			import random
			_uuid=str(random.randint(0,100000000))
			cur.execute("UPDATE login SET value=%s WHERE property='uuid'",(_uuid,))
			conn.commit()
			cur.close()
			pool.putconn(conn)
			web.setcookie("uuid", _uuid, 3600)
			web.seeother("/")
		else:
			web.seeother("/login?fail=1")

class logout:
	def GET(self):
		web.setcookie("uuid","", expires=-1)
		web.seeother("/")

def getmapstable(group):
	conn = pool.getconn()
	cur=conn.cursor()
	cur.execute("SELECT mac,ip FROM maps WHERE parentgroup=%s",(group,))
	table=render.table(cur.fetchall(),group)
	cur.close()
	pool.putconn(conn)
	return table

class maps:
	def GET(self):
		checkauthorisation()
		group=web.input(group=None).group
		conn=pool.getconn()
		cur=conn.cursor()
		cur.execute("SELECT name FROM groups WHERE id=%s",(group,))
		group=group if (cur.rowcount>0) else None
		cur.close()
		pool.putconn(conn)
		if group is not None:
			return render.layout(getmapstable(group))
		else:
			return render.layout("Unrecognized Group")
	def POST(self):
		checkauthorisation()
		conn=pool.getconn()
		cur=conn.cursor()
		if not web.input(delmac=None).delmac is None:
			cur.execute("DELETE FROM maps WHERE mac=%s",(web.input().delmac,))
			conn.commit()
			cur.close()
			pool.putconn(conn)
			return web.seeother("/maps?group="+web.input().group)
		elif not web.input(add_mac=None).add_mac is None:
			cur.execute("SELECT subnet,serial FROM subnets LIMIT 1")
			row=cur.fetchone();
			subnet=row[0]
			serial=row[1]
			cur.execute("SELECT start_ip_int,end_ip_int FROM groups WHERE id=%s",(web.input().group,))
			row=cur.fetchone()
			macs=web.input().macs.split(",")
			cur.execute("select t.ip as missing from generate_series(%s, %s) t(ip) left join maps  on (t.ip = maps.ip) where maps.ip is null limit %s",(row[0],row[1],len(macs)))
			ips=cur.fetchall();
			query="INSERT INTO maps (mac,ip,hostname,subnet,serial,parentgroup) VALUES "
			for idx,mac in enumerate(macs):
				if(idx>0):
					query+=","
				query+="('%s',%s,NULL,'%s',%s,%s)" % (mac, ips[idx][0], subnet, serial, web.input().group)
			
			cur.execute(query)
			conn.commit()
			cur.close()
			pool.putconn(conn)
			return web.seeother("/maps?group="+web.input().group)
		else:
			return web.seeother("/maps?group="+web.input().group)
def webserver():
	app = web.application(urls, globals())
	app.run()
